import { getDatabase } from '../database/database'
import type { User, CreateUserRequest, UpdateUserRequest } from '../models/User'

export class UserService {
  // 获取所有用户
  getAllUsers(): User[] {
    const db = getDatabase()
    const result = db.exec('SELECT * FROM users ORDER BY created_at DESC')
    if (result.length === 0 || !result[0]) return []
    
    const columns = result[0].columns
    const values = result[0].values
    
    return values.map((row: any[]) => {
      const user: any = {}
      columns.forEach((col: string, index: number) => {
        user[col] = row && row[index] !== undefined ? row[index] : null
      })
      return user as User
    })
  }

  // 根据ID获取用户
  getUserById(id: number): User | null {
    const db = getDatabase()
    const result = db.exec('SELECT * FROM users WHERE id = ?', [id])
    if (result.length === 0 || !result[0] || result[0].values.length === 0) return null
    
    const columns = result[0].columns
    const row = result[0].values[0]
    
    const user: any = {}
    columns.forEach((col: string, index: number) => {
      user[col] = row && row[index] !== undefined ? row[index] : null
    })
    return user as User
  }

  // 创建用户
  createUser(userData: CreateUserRequest): User {
    const db = getDatabase()
    
    db.run(
      'INSERT INTO users (name, email, age) VALUES (?, ?, ?)',
      [userData.name, userData.email, userData.age]
    )

    // 获取最后插入的ID
    const result = db.exec('SELECT last_insert_rowid() as id')
    if (result.length === 0 || !result[0] || result[0].values.length === 0 || !result[0].values[0]) {
      throw new Error('Failed to get last insert ID')
    }
    const newId = result[0].values[0][0] as number

    const newUser = this.getUserById(newId)
    if (!newUser) {
      throw new Error('Failed to create user')
    }

    return newUser
  }

  // 更新用户
  updateUser(id: number, userData: UpdateUserRequest): User | null {
    const db = getDatabase()
    
    // 构建更新字段
    const updates: string[] = []
    const values: any[] = []

    if (userData.name !== undefined) {
      updates.push('name = ?')
      values.push(userData.name)
    }
    if (userData.email !== undefined) {
      updates.push('email = ?')
      values.push(userData.email)
    }
    if (userData.age !== undefined) {
      updates.push('age = ?')
      values.push(userData.age)
    }

    if (updates.length === 0) {
      throw new Error('No fields to update')
    }

    updates.push('updated_at = CURRENT_TIMESTAMP')
    values.push(id)

    const query = `UPDATE users SET ${updates.join(', ')} WHERE id = ?`
    
    db.run(query, values)
    
    return this.getUserById(id)
  }

  // 删除用户
  deleteUser(id: number): boolean {
    const db = getDatabase()
    db.run('DELETE FROM users WHERE id = ?', [id])
    
    // 检查是否删除了行
    const result = db.exec('SELECT changes() as changes')
    if (result.length === 0 || !result[0] || result[0].values.length === 0 || !result[0].values[0]) {
      return false
    }
    const changes = result[0].values[0][0] as number
    return changes > 0
  }

  // 检查邮箱是否已存在
  isEmailExists(email: string, excludeId?: number): boolean {
    const db = getDatabase()
    let query = 'SELECT COUNT(*) as count FROM users WHERE email = ?'
    const params: any[] = [email]

    if (excludeId) {
      query += ' AND id != ?'
      params.push(excludeId)
    }

    const result = db.exec(query, params)
    if (result.length === 0 || !result[0] || result[0].values.length === 0 || !result[0].values[0]) {
      return false
    }
    const count = result[0].values[0][0] as number
    return count > 0
  }
}
